﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;
using MySql.Data.MySqlClient;
using VyAutoBuildEntity.Plugin.Model;

namespace VyAutoBuildEntity.Plugin.Common
{
    internal class DataContext
    {
        private readonly string _connectionString;
        private readonly DataBaseType _dataBaseType;

        public DataContext(string connectionString, DataBaseType dataBaseType)
        {
            _connectionString = connectionString;
            _dataBaseType = dataBaseType;
        }
        #region 表
        private List<TableColumnModel> GetMysqlTableColumn()
        {
            using (var conn = new MySqlConnection(_connectionString))
            {
                var sql =
                    $@"select c.COLUMN_NAME as `Name`,CASE c.COLUMN_KEY WHEN 'PRI' THEN 'PK' ELSE '' END as KeyType,
c.DATA_TYPE as DataType ,c.IS_NULLABLE as IsNull,c.COLUMN_DEFAULT as DefaultValue,COLUMN_COMMENT as Remark,c.TABLE_NAME  as TableName
from information_schema.COLUMNS as c 
where c.TABLE_SCHEMA='{conn.Database}'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION;";
                conn.Open();
                var result = conn.Query<TableColumnModel>(sql).ToList();
                conn.Close();
                return result;
            }
        }

        private List<TableColumnModel> GetMsSqlTableColumn()
        {
            return null;
        }

        public List<TableColumnModel> GetTableColumn()
        {
            switch (_dataBaseType)
            {
                case DataBaseType.MySql:
                    return GetMysqlTableColumn();
                case DataBaseType.MsSql:
                    return GetMsSqlTableColumn();
                default:
                    throw new ArgumentOutOfRangeException("数据库类型不正确");
            }
        }

        #endregion

        #region 视图
        public List<TableColumnModel> GetViewColumn()
        {
            switch (_dataBaseType)
            {
                case DataBaseType.MySql:
                    return GetMysqlViewColumn();
                case DataBaseType.MsSql:
                    return GetMsSqlViewColumn();
                default:
                    throw new ArgumentOutOfRangeException("数据库类型不正确");
            }
        }
        private List<TableColumnModel> GetMysqlViewColumn()
        {
            using (var conn = new MySqlConnection(_connectionString))
            {
                var sql =
                    $@"select c.COLUMN_NAME as `Name`,CASE c.COLUMN_KEY WHEN 'PRI' THEN 'PK' ELSE '' END as KeyType,
c.DATA_TYPE as DataType ,c.IS_NULLABLE as IsNull,c.COLUMN_DEFAULT as DefaultValue,COLUMN_COMMENT as Remark,c.TABLE_NAME  as TableName
from information_schema.COLUMNS as c 
JOIN information_schema.`VIEWS` as v on c.TABLE_NAME=v.TABLE_NAME
where c.TABLE_SCHEMA='{conn.Database}'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION;";
                conn.Open();
                var result = conn.Query<TableColumnModel>(sql).ToList();
                conn.Close();
                return result;
            }
        }

        private List<TableColumnModel> GetMsSqlViewColumn()
        {
            return null;
        }
        #endregion

        public string GetSql(List<TableColumnModel> columns, string tableName, string alias)
        {
            switch (_dataBaseType)
            {
                case DataBaseType.MySql:
                    return GetMySqlSql(columns, tableName, alias);
                case DataBaseType.MsSql:
                    return GetMsSqlSql(columns, tableName, alias);
                    ;
                default:
                    throw new ArgumentOutOfRangeException("数据库类型不正确");
            }
        }

        private string GetMySqlSql(List<TableColumnModel> columns, string tableName, string alias)
        {
            var sql = new StringBuilder("select ");
            if (string.IsNullOrEmpty(alias))
            {
                foreach (var item in columns)
                    sql.AppendFormat("`{0}`,", item.Name);
                return $"{sql.ToString().Trim(',')} FROM `{tableName}` where ";
            }

            foreach (var item in columns)
                sql.AppendFormat("{0}.`{1}` as `{2}`,", alias, item.Name, item.CSharpName);
            return $"{sql.ToString().Trim(',')} FROM `{tableName}` as `{alias}` where ";
        }

        private string GetMsSqlSql(List<TableColumnModel> columns, string tableName, string alias)
        {
            var sql = new StringBuilder("select ");
            if (string.IsNullOrEmpty(alias))
            {
                foreach (var item in columns)
                    sql.AppendFormat("[{0}],", item.Name);
                return $"{sql.ToString().Trim(',')} FROM [{tableName}] where ";
            }

            foreach (var item in columns)
                sql.AppendFormat("{0}.[{1}] as [{2}],", alias, item.Name, item.CSharpName);
            return $"{sql.ToString().Trim(',')} FROM [{tableName}] as {alias} where ";
        }
    }

    internal enum DataBaseType
    {
        MySql = 1,
        MsSql = 2
    }
}